Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Rule-Based Approach

The rule-based approach to Oracle optimization is the simpler of the two methods. In the rule-based approach, the execution plan is derived by examining the available paths and comparing them against a table of the rank of these paths. The table of costs is shown in Table 27.1.

Table 27.1 Cost of Access Paths for Rule-Based Optimization

Rank Access Path

1 Single row by ROWID
2 Single row by cluster join
3 Single row by hash cluster key with unique or primary key
4 Single row by unique or primary key
5 Cluster join
6 Hash cluster key
7 Indexed cluster key
8 Composite key
9 Single-column indexes
10 Bounded range search on indexed columns
11 Unbounded range search on indexed columns
12 Sort-merge join
13 MAX or MIN of indexed column
14 ORDER BY on indexed columns
15 Full-table scan

Because the rule-based approach is based simply on the SQL statements themselves, it is unnecessary to have any statistics about the database tables. The rule-based approach follows these steps to determine the execution plan:

1.  Determine the possible execution plans.
2.  Rank the different plans according to Table 27.1.
3.  Choose the approach with the lowest ranking.

In this way, the rule-based optimization approach is very efficient and works well. However, if statistics are available for your tables, clusters, or indexes, the cost-based approach can be very efficient.

Cost-Based Approach

The cost-based approach to optimization uses information about your database to choose the most efficient execution plan. During the normal operation of the RDBMS, or when you execute the ANALYZE command, statistics are gathered on the data distribution and storage characteristics for your database tables, clusters, and indexes. The cost-based optimizer uses this information to determine the most optimal execution plan.

This approach is done in three steps:

1.  The optimizer generates a set of possible execution plans, just as the rule-based optimization approach does.
2.  The cost of each plan is determined based on statistics gathered about the database. This cost is based on CPU time, I/O, and memory necessary to execute the plan.
3.  The optimizer compares the costs and chooses the execution plan with the smallest cost based on your specifications.

The default goal of the cost-based optimizer is to generate an execution plan that gives the best throughput. You can specify other optimization goals, including the following:


Optimization Goal Description

Minimal Resources This goal causes the optimizer to choose the execution plan that uses the least amount of system resources.
Best Response Time This goal causes the optimizer to choose the execution plan that has the best response time.

By choosing the optimization approach that best suits your particular installation and application, the performance of your SQL statements can be tuned to specifically meet your needs.

Using the ANALYZE Command

You can use the ANALYZE command to gather statistics about your system that can be used for the cost-based optimizer. This command can be used not only for statistics gathering but for other purposes as well. The ANALYZE command can be used to do the following:


Function Description

Gather statistics The ANALYZE command can be used to gather statistics about tables, clusters, and indexes that can assist the cost-based optimizer in choosing the best execution plan for your system.
Check data integrity The ANALYZE command can be used to validate the integrity of the structure of a table, index, or cluster.
Chained-row statistics The ANALYZE command can be used to gather statistics about the number of chained rows in a table or cluster.

The statistics gathered by the ANALYZE command can better help the optimizer make the correct choice in determining an execution plan.

How To Run the ANALYZE Command

How you run the ANALYZE command is determined by the type of statistics or analysis you want to perform. The ANALYZE command can be used in several different modes. The mode you choose depends on the data you want to gather as well as on the configuration of your system.

Using ANALYZE To Gather Statistics

You can use the ANALYZE command to gather statistics in one of two modes. The first mode scans the entire table, cluster, or index and calculates statistics exactly, based on your data. Although this is the most accurate method, it requires enough temporary space to hold and sort all the rows of the table or cluster (no space is required for an index). Computing the statistics also uses a great deal of system resources.

The second mode of the ANALYZE command estimates statistics. This method performs a sampling of the table, cluster, or index in order to estimate statistics. In this method, the entire table or cluster is not scanned; a portion of the data is used to determine the statistics. The amount of data used can be specified when you invoke the ANALYZE command.

Using ANALYZE To Compute Exact Statistics

To use the ANALYZE command to compute exact statistics, invoke ANALYZE with one of the following syntaxes.

For Tables:

ANALYZE TABLE table_name
    COMPUTE STATISTICS;

For Clusters:

ANALYZE CLUSTER cluster_name
    COMPUTE STATISTICS;

For Indexes:

ANALYZE INDEX index_name
    COMPUTE STATISTICS;


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.